const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const { logger } = require('./logger');

// 数据库文件路径
const DB_PATH = path.join(__dirname, '../../data/blue-device-manger.db');

// 确保data目录存在
const fs = require('fs');
const dataDir = path.dirname(DB_PATH);
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

// 创建数据库连接
const db = new sqlite3.Database(DB_PATH, (err) => {
  if (err) {
    logger.error('SQLite数据库连接失败:', err);
  } else {
    logger.info('SQLite数据库连接成功');
  }
});

// 初始化数据库表
const initDatabase = () => {
  // 创建蓝牙设备表
  const createBlueDeviceTableSQL = `
    CREATE TABLE IF NOT EXISTS blue_devices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      mac_address TEXT UNIQUE NOT NULL,
      device_name TEXT NOT NULL,
      device_type TEXT DEFAULT 'bluetooth',
      device_status TEXT DEFAULT 'active',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      last_used_at INTEGER,
      notes TEXT
    );
  `;

  db.run(createBlueDeviceTableSQL, (err) => {
    if (err) {
      logger.error('创建蓝牙设备表失败:', err);
    } else {
      logger.info('蓝牙设备表创建/检查完成');
    }
  });

  // 创建索引提高查询性能
  const createIndexSQL = `
    CREATE INDEX IF NOT EXISTS idx_mac_address ON blue_devices(mac_address);
    CREATE INDEX IF NOT EXISTS idx_device_status ON blue_devices(device_status);
    CREATE INDEX IF NOT EXISTS idx_created_at ON blue_devices(created_at);
  `;

  db.exec(createIndexSQL, (err) => {
    if (err) {
      logger.error('创建索引失败:', err);
    } else {
      logger.info('蓝牙设备表索引创建完成');
    }
  });

  // 如果环境变量中有默认设备，自动添加
  if (process.env.ALLOWED_SERIAL) {
    const currentTimestamp = Math.floor(Date.now() / 1000);
    const insertDefaultDeviceSQL = `
      INSERT OR IGNORE INTO blue_devices
      (mac_address, device_name, device_type, device_status, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?)
    `;

    db.run(insertDefaultDeviceSQL, [
      process.env.ALLOWED_SERIAL,
      '默认设备',
      'bluetooth',
      'active',
      currentTimestamp,
      currentTimestamp
    ], function(err) {
      if (err) {
        logger.error('添加默认设备失败:', err);
      } else if (this.changes > 0) {
        logger.info('默认设备已添加到数据库');
      }
    });
  }
};

// 清理非活跃设备的定时任务
const cleanupInactiveDevices = () => {
  const thirtyDaysAgo = Math.floor(Date.now() / 1000) - (30 * 24 * 60 * 60);
  const cleanupSQL = `
    UPDATE blue_devices
    SET device_status = 'inactive', updated_at = ?
    WHERE last_used_at < ? AND device_status = 'active'
  `;

  db.run(cleanupSQL, [Math.floor(Date.now() / 1000), thirtyDaysAgo], function(err) {
    if (err) {
      logger.error('清理非活跃设备失败:', err);
    } else if (this.changes > 0) {
      logger.info(`标记了 ${this.changes} 个设备为非活跃状态`);
    }
  });
};

// 每天清理一次非活跃设备
setInterval(cleanupInactiveDevices, 24 * 60 * 60 * 1000);

// 优雅关闭数据库
const closeDatabase = () => {
  return new Promise((resolve, reject) => {
    db.close((err) => {
      if (err) {
        logger.error('关闭数据库失败:', err);
        reject(err);
      } else {
        logger.info('数据库连接已关闭');
        resolve();
      }
    });
  });
};


module.exports = {
  db,
  initDatabase,
  closeDatabase,
  cleanupInactiveDevices
};